package com.anolesoft.yr.mbus.pre.sql;

public class PreVisitSQL {

    //新增时查询拜访记录编号是否已经存在
    public static final String QUERY_PRE_VISIT_BY_CODE = "select count(*) as count from pre_visit a where a.del_flag=0 and a.visit_code=#{visitCode}";

    //新增拜访客户记录
    public static final String SAVE_PRE_VISIT = "insert into pre_visit(objuid,fac_code,crm_base_uid,visit_code,visit_time,client_name,client_region,contact,contact_person,client_demand,purchase_quantity,sales_person,remark,c_user_uid,c_time,del_flag) values(#{objuid},#{facCode},#{crmBaseUid},#{visitCode},#{visitTime},#{clientName},#{clientRegion},#{contact},#{contactPerson},#{clientDemand},#{purchaseQuantity},#{salesPerson},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //修改时查询拜访记录编号是否已经存在（不包含本身）
    public static final String QUERY_PRE_VISIT_BY_CODE2 = "select count(*) as count from pre_visit a where a.del_flag=0 and a.visit_code=#{visitCode} and a.visit_code<>#{visitCode2}";

    //修改拜访记录
    public static final String UPDATE_PRE_VISIT = "update pre_visit a set a.visit_code=#{visitCode},a.visit_time=#{visitTime},client_name=#{clientName},a.client_region=#{clientRegion},a.contact=#{contact},a.contact_person=#{contactPerson},a.client_demand=#{clientDemand},a.purchase_quantity=#{purchaseQuantity},a.sales_person=#{salesPerson},a.remark=#{remark},a.m_user_uid=#{mmUserUid},a.m_time=#{mmTime} where a.objuid=#{objuid}";

    //删除拜访记录
    public static final String DELETE_PRE_VISIT = "update pre_visit a set a.del_flag=1 where a.objuid=#{objuid}";

    //根据主键查询拜访记录唯一数据
    public static final String QUERY_PRE_VISIT_BY_UID = "select a.objuid,a.visit_code,date_format(a.visit_time,'%Y-%m-%d') as visit_time,a.base_region_objuid as client_region_,case when a.base_region_objuid is null then a.client_region else a.region_name end as client_region,a.crm_base_objuid as client_name_,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.sales_person_,a.sales_person,a.remark from (select a.objuid,a.visit_code,visit_time,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.sales_person as sales_person_,c.emp_name as sales_person,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_visit a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.sales_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0) a where 1=1 and a.objuid=#{objuid}";

    //根据条件查询拜访客户记录条数
    public static final String QUERT_PRE_VISIT_COUNT = "<script> select count(*) as count from (select a.objuid,a.visit_code,date_format(a.visit_time,'%Y-%m-%d') as visit_time,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as sales_person,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_visit a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.sales_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0) a where 1 = 1  <if test=\"visitTime!=''\"> and upper(a.visit_time) like upper(concat('%',#{visitTime},'%')) </if> <if test=\"clientName!=''\"> and upper(a.client_name) like upper(concat('%',#{clientName},'%')) </if> <if test=\"salesPerson!=''\"> and upper(a.sales_person) = upper(#{salesPerson}) </if> </script>";

    //查询拜访客户记录
    public static final String QUERT_PRE_VISIT = "<script> select a.objuid,a.visit_code,a.visit_time,case when a.base_region_objuid is null then a.client_region else a.region_name end as client_region,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,a.sales_person,a.remark from (select a.objuid,a.visit_code,date_format(a.visit_time,'%Y-%m-%d') as visit_time,a.client_region,a.client_name,a.contact,a.contact_person,a.client_demand,a.purchase_quantity,c.emp_name as sales_person,a.remark,b.region_name,b.objuid as base_region_objuid,d.crm_name,d.objuid as crm_base_objuid from pre_visit a left join base_region b on b.objuid=a.client_region left join fac_employee c on c.objuid=a.sales_person left join crm_base d on d.objuid=a.client_name where a.del_flag=0) a where 1 = 1  <if test=\"visitTime!=''\"> and upper(a.visit_time) like upper(concat('%',#{visitTime},'%')) </if> <if test=\"clientName!=''\"> and upper(a.client_name) like upper(concat('%',#{clientName},'%')) </if> <if test=\"salesPerson!=''\"> and upper(a.sales_person) = upper(#{salesPerson}) </if> order by a.visit_time desc,a.client_name asc limit #{page},#{limit} </script>";

}
